In this sixth and final installment in this introductory series on JSON I’d like to revisit the question of generating JSON from your Oracle database. Cos it’s great to be able to consume a format; but if you can’t spit it out easily too you’ll probably want to look elsewhere.

Easily, that’s the keyword in that sentence. In my previous article, we examined the various solutions for generating JSON data from an Oracle database — building it manually, using APEX_JSON, and PL/JSON —  and, frankly, they ranged from painful to very painful. What we need, ideally, is a solution that ticks two boxes:

• it should be a baked-in solution that doesn’t require any additional installation. Which rules out PL/JSON and, for many, APEX_JSON.
• it should be easy to use. Which rules out generating JSON data using with statements and analytical functions, and, for many, rules out PL/JSON too.

Starting with 12c Release 2, Oracle believe they have provided a solution that ticks both boxes. It is February 2017 as I write these words, and so it’s all still very new and up-in-the-air (indeed, 12.2 functionality is only accessible via Oracle’s Exadata Express Cloud Service). This may still be the situation when you’re reading this article, or you may be reading so far in the future that civilisation has collapsed and Justin Bieber is Prime Minister of Canada (it’s gonna happen, Canada; get ready!). Either way, here are the SQL/JSON Generation functions.

SQL/JSON

The official documentation on the SQL/JSON Generation functions, in my opinion, tacitly acknowledges that this functionality is coming very, very late in the game by slagging off the alternative methods of generating JSON; they are, it says, error-prone and inefficient.

SQL/JSON has the following advantages:

• It is easy to use since it is similar to well-known SQL/XML functionality.
• It generates JSON directly from SQL, not PL/SQL.
• Network overheads are minimised, as only the generated document is returned to the client. 
• Using subqueries and other simple SQL tricks, you can generate very complex JSON documents in a single query.
• Its JSON documents are always well-formed.

But that’s enough foreplay; let’s dive in and get our hands our hands on the functions themselves.

JSON_OBJECT

The JSON_OBJECT function eats name-value pairings and poops out a JSON object. Both halves of the pair must be explicitly expressed; the name must evaluate to an SQL identifier, while the value can be an SQL expression. They must be separated by the keyword VALUE.

Speaking of the input that JSON_OBJECT accepts – and this is true for some of the other functions we’ll be talking about later – the input for value can be JSON data or a call to an SQL/JSON function.  This is why it is possible to generate reasonably complex JSON documents from a single query.  I’ll show you what I mean. 

Let’s throw together a query on a table containing staff details, and let’s have a nested call to JSON_OBJECT in it to see what that does.

JSON_ARRAY

JSON_ARRAY is, unsurprisingly, the equivalent function for putting together JSON arrays. It works pretty much as you’d expect; it constructs an array from the values it is fed, and those arguments can come in the form of any SQL expression, including, of course, a call to another SQL/JSON function. The eventual array element order will mirror that of the supplied arguments.

I’ll show you what I mean. Let’s generate an array from a ragtag gang of unrelated things and see what we get. 

JSON_ARRAY, just like JSON_OBJECT, is pretty straightforward and, as a popular British advert goes, it does exactly what it says on the tin. Both functions can accept a varying number of arguments, but they will always return JSON fragments made up of the exact number of elements they received as arguments. If you require more intelligence or complexity you will need to look in the direction of…

JSON_OBJECTAGG

JSON_OBJECTAGG constructs a JSON object by aggregating the resultset of an SQL query. This means that we can easily build an list of JSON objects containing our various departments with the shortest of queries.

JSON_ARRAYAGG

The JSON_ARRAYAGG function returns an array based on the aggregated results of a grouped SQL query. The size of the resultant array is wholly dependent on the data returned by the query, expanding and contracting to fit the resultset; this makes it differ from JSON_ARRAY which can only rigidly return an array the size of the arguments fed it. 

As you probably expect, you can use JSON_ARRAYAGG in conjunction with other SQL/JSON functions to begin to build intricate JSON documents.  We could, for example, generate a document containing a list of departments and their staff by simply combining a call to JSON_OBJECT with one to JSON_ARRAYAGG.

Conclusion

This has been a rather quick introduction to the new SQL/JSON functions. Part of the reason for this is that my main aim is to get you to a state of easy familiarity with them and what they can do; complexity can come later. However, it is also due to the time at which I’m writing; as I’ve said, all of this is – in February 2017 – still very new and exciting to all of us.

Superficial as it might be, I hope that this article has done enough to make you aware of the power of these functions, of the ways in which they can be combined to construct complex JSON documents, of the advantages they hold over many of the other methods that exist for generating JSON from an Oracle database.

Yes? Then good, I can end this series here.